package com.zl.learning.db.sqlite;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.function.Consumer;

public class SqliteTest {

    private SQLiteConfig config = new SQLiteConfig();
    private SQLiteDataSource dataSource = new SQLiteDataSource();

    {
        config.setBusyTimeout(30000);
        dataSource.setUrl("jdbc:sqlite:test.db");
        dataSource.setConfig(config);
    }

    private ExecutorService executorService = Executors.newFixedThreadPool(100);
    private QueryRunner queryRunner = new QueryRunner(dataSource);

    @Test
    public void test1() throws SQLException {

        List<Map<String, Object>> res = queryRunner.query("SELECT * FROM COMPANY", new MapListHandler());
        res.forEach(r -> r.forEach((k, v) -> System.out.println(k + "---" + v)));
        query("SELECT * FROM COMPANY", r -> {
            try {
                while (r.next()) {
                    System.out.println(r.getObject(1));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });


        queryRunner.query("", rs -> {
            String aa = "";
            while (rs.next()) {

            }
            return aa;
        });

    }

    @Test
    public void test2() throws SQLException {
        Connection conn = dataSource.getConnection();
        conn.close();
        System.out.println("a");
    }

    @Test
    public void test3() {
        start();
        try {
            TimeUnit.MINUTES.sleep(5);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }

    private <R> void query(String sql, Consumer<ResultSet> consumer) {
        try {
            Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            consumer.accept(rs);
            rs.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void start() {
        new Thread(new T1()).start();
        new Thread(new T2()).start();
    }

    private class T1 implements Runnable {

        @Override
        public void run() {
            for (int i = 0; i < 100; i++) {
                String sql = "SELECT * FROM COMPANY";
                executorService.execute(() -> {
                    List<Map<String, Object>> res = null;
                    try {
                        res = queryRunner.query(sql, new MapListHandler());
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    System.out.println("Read:");
                });
            }
            executorService.shutdown();
        }
    }

    private class T2 implements Runnable {

        @Override
        public void run() {
            for (int i = 0; i < 100; i++) {
                String sql = "INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ('Allen', 25, 'Texas', 15000.00 )";
                executorService.execute(() -> {
                    try {
                        queryRunner.update(sql);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    System.out.println("Write:");
                });
            }
            executorService.shutdown();
        }
    }
}
